package com.ecsolutions.service;

import com.ecsolutions.dao.ExcelDownld_DAO;
import com.ecsolutions.entity.ExcelDownld_Entity;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.io.FileOutputStream;
import java.io.IOException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

/**
 * Created by Administrator on 2017/8/17.
 */
@Service("ExcelDownld_Service")
public class ExcelDownld_ServiceImpl implements ExcelDownld_Service {
    private ExcelDownld_DAO excelDownld_dao;
    @Autowired
    public ExcelDownld_ServiceImpl(ExcelDownld_DAO excelDownld_dao) {
        this.excelDownld_dao= excelDownld_dao;
    }

        /**
     *字符串的日期格式的计算
     */
    public static int daysBetween(String smdate,String bdate) throws ParseException{
        SimpleDateFormat sdf=new SimpleDateFormat("yyyyMMdd");
        Date date1=sdf.parse(smdate);
        Date date2=sdf.parse(bdate);

        int days = (int) ((date2.getTime() - date1.getTime()) / (1000*3600*24));
        return days;
    };

@Override
    public void ExcelDownld(ExcelDownld_Entity excelDownld_entity){
        String LOANREF =excelDownld_entity.getLOANREF();
        ExcelDownld_Entity UtilizationdetailInfo = excelDownld_dao.getUtilizationdetailInfo(LOANREF);
        List<ExcelDownld_Entity> PaymentScheduleInfo = excelDownld_dao.getPaymentScheduleInfo(LOANREF);
        ExcelDownld_Entity SumInfo = excelDownld_dao.getSumInfo(LOANREF);


        Workbook wb = new HSSFWorkbook();
        Sheet sheet = wb.createSheet("还款计划表");
        Row row2 = sheet.createRow((short) 2);
        sheet.addMergedRegion(new CellRangeAddress(  2,2, 0, 6));// 设置单元格合并

        CellStyle centerStyle = wb.createCellStyle(); //居中样式
        centerStyle.setAlignment(HorizontalAlignment.CENTER);
        centerStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        CellStyle blackBoderStyle = wb.createCellStyle();//黑边框
        //设置边框样式
        blackBoderStyle.setBorderTop(BorderStyle.THIN);
        blackBoderStyle.setBorderBottom(BorderStyle.THIN);
        blackBoderStyle.setBorderLeft(BorderStyle.THIN);
        blackBoderStyle.setBorderRight(BorderStyle.THIN);
        //设置边框颜色
        blackBoderStyle.setBottomBorderColor(IndexedColors.BLACK.index);
        blackBoderStyle.setBottomBorderColor(IndexedColors.BLACK.index);
        blackBoderStyle.setLeftBorderColor(IndexedColors.BLACK.index);
        blackBoderStyle.setRightBorderColor(IndexedColors.BLACK.index);
        row2.createCell(0).setCellValue("还款计划表草案");
        row2.getCell(0).setCellStyle(centerStyle);

        Row row4 = sheet.createRow((short) 4);
        row4.createCell(0).setCellValue("贷款总额："+UtilizationdetailInfo.getDrawamt());

        Row row5 = sheet.createRow((short) 5);
        row5.createCell(0).setCellValue("货币："+UtilizationdetailInfo.getDrawccy());

        Row row6 = sheet.createRow((short) 6);
        sheet.addMergedRegion(new CellRangeAddress(  6,6, 0, 1));// 设置单元格合并
        row6.createCell(0).setCellValue("贷款拨付/发放日期");
        row6.getCell(0).setCellStyle(centerStyle);
        row6.getCell(0).setCellStyle(blackBoderStyle);
        row6.createCell(2).setCellValue("贷款总额");
        row6.getCell(2).setCellStyle(blackBoderStyle);
        row6.createCell(4).setCellValue("年利率（%）");
        sheet.addMergedRegion(new CellRangeAddress(  6,6, 4, 5));// 设置单元格合并
        row6.getCell(4).setCellStyle(centerStyle);
        row6.getCell(4).setCellStyle(blackBoderStyle);
        row6.createCell(6).setCellValue("起息日期");
        row6.getCell(6).setCellStyle(blackBoderStyle);
        row6.getCell(6).setCellStyle(blackBoderStyle);

        Row row7 = sheet.createRow((short) 7);
        sheet.addMergedRegion(new CellRangeAddress(  7,7, 0, 1));// 设置单元格合并
        row7.createCell(0).setCellValue(UtilizationdetailInfo.getDrawdate());
        row7.getCell(0).setCellStyle(blackBoderStyle);
        row7.createCell(2).setCellValue(UtilizationdetailInfo.getDrawamt());
        row7.getCell(2).setCellStyle(blackBoderStyle);
        sheet.addMergedRegion(new CellRangeAddress(  7,7, 4, 5));// 设置单元格合并
        row7.createCell(4).setCellValue(UtilizationdetailInfo.getSchemarate());
        row7.getCell(4).setCellStyle(blackBoderStyle);
        row7.createCell(6).setCellValue(UtilizationdetailInfo.getDrawdate());
        row7.getCell(6).setCellStyle(blackBoderStyle);

        Row row9 = sheet.createRow((short) 9);
        row9.createCell(0).setCellValue("期数");
        row9.createCell(1).setCellValue("日期");
        row9.createCell(2).setCellValue("计息天数");
        row9.createCell(3).setCellValue("还款金额");
        row9.createCell(4).setCellValue("本金");
        row9.createCell(5).setCellValue("利息");
        row9.createCell(6).setCellValue("余额");
        row9.getCell(0).setCellStyle(blackBoderStyle);
        row9.getCell(1).setCellStyle(blackBoderStyle);
        row9.getCell(2).setCellStyle(blackBoderStyle);
        row9.getCell(3).setCellStyle(blackBoderStyle);
        row9.getCell(4).setCellStyle(blackBoderStyle);
        row9.getCell(5).setCellStyle(blackBoderStyle);
        row9.getCell(6).setCellStyle(blackBoderStyle);


try {
    for (int i = 0; i < PaymentScheduleInfo.size(); i++) {
        Row row9plus = sheet.createRow((short) (10 + i));
        row9plus.createCell(0).setCellValue(PaymentScheduleInfo.get(i).getInstno());
        row9plus.createCell(1).setCellValue(PaymentScheduleInfo.get(i).getEnddate());
        if (i == 0) {
            row9plus.createCell(2).setCellValue(daysBetween(UtilizationdetailInfo.getDrawdate(),  PaymentScheduleInfo.get(i).getEnddate()));
//            System.out.println(UtilizationdetailInfo.getDrawdate()+""+PaymentScheduleInfo.get(i).getEnddate());
//            System.out.println(daysBetween(UtilizationdetailInfo.getDrawdate(),  PaymentScheduleInfo.get(i).getEnddate()));
        } else {
            row9plus.createCell(2).setCellValue(daysBetween(PaymentScheduleInfo.get(i - 1).getEnddate(), PaymentScheduleInfo.get(i).getEnddate()));
//            System.out.println(PaymentScheduleInfo.get(i - 1).getEnddate()+""+PaymentScheduleInfo.get(i).getEnddate());
        }
        row9plus.createCell(3).setCellValue(PaymentScheduleInfo.get(i).getInstamt());
        row9plus.createCell(4).setCellValue(PaymentScheduleInfo.get(i).getPrnamt());
        row9plus.createCell(5).setCellValue(PaymentScheduleInfo.get(i).getIntamt());
        row9plus.createCell(6).setCellValue(PaymentScheduleInfo.get(i).getOsamt());
        for (int j = 0; j < 7; j++) {
            row9plus.getCell(j).setCellStyle(blackBoderStyle);
        }
    }
}
catch (ParseException e){
    e.printStackTrace();
}

        Row row10plusInstno = sheet.createRow((short) (10+PaymentScheduleInfo.size()));//合计行
        sheet.addMergedRegion(new CellRangeAddress(  10+PaymentScheduleInfo.size(),10+PaymentScheduleInfo.size(), 0, 2));// 设置单元格合并
        row10plusInstno.createCell(0).setCellValue("合计: "+UtilizationdetailInfo.getTenor()+"期");
        row10plusInstno.createCell(3).setCellValue(SumInfo.getSum_instamt());
        row10plusInstno.createCell(4).setCellValue(SumInfo.getSum_prnamt());
        row10plusInstno.createCell(5).setCellValue(SumInfo.getSum_intamt());
        row10plusInstno.createCell(6).setCellValue("");
        row10plusInstno.getCell(0).setCellStyle(blackBoderStyle);
        row10plusInstno.getCell(3).setCellStyle(blackBoderStyle);
        row10plusInstno.getCell(4).setCellStyle(blackBoderStyle);
        row10plusInstno.getCell(5).setCellStyle(blackBoderStyle);
        row10plusInstno.getCell(6).setCellStyle(blackBoderStyle);







        //创建一个文件 命名为workbook.xls
        try {
            FileOutputStream fileOut = new FileOutputStream("C:\\Users\\Administrator\\Desktop\\workbook.xls");
            // 把上面创建的工作簿输出到文件中
            wb.write(fileOut);
            //关闭输出流
            fileOut.close();
        }catch(IOException e){
            e.printStackTrace();
        }


    }
}
